package com.bbcare.exportExcel.plat.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

public class InsertDemoData {

    public static String base = "abcdefghijklmnopqrstuvwxyz0123456789";   
    private static String firstName="赵赵赵赵赵赵赵赵赵赵赵赵赵赵赵赵钱钱钱钱钱孙孙孙李李李李李李李李李李李李李李李李周周周吴吴吴吴吴吴郑王王王王王王王王王王王王冯冯冯冯陈陈陈陈褚卫蒋沈韩韩韩杨杨杨朱朱朱秦秦秦尤许何吕施张张张张张张孔孔孔曹曹曹曹严华金魏魏魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘缪干解应宗宣丁贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊於惠甄魏加封芮储靳汲邴糜松井段富乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫宁仇栾暴甘钭厉戎祖武符刘姜詹束龙叶幸司韶郜黎蓟薄印宿白怀蒲台从鄂索咸籍赖卓蔺屠蒙池乔阴郁胥能苍双闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍却璩桑桂濮牛寿通边扈燕冀郏浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容向古易慎戈廖庚终暨居衡步都耿满弘匡国文寇广禄阙东殴沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空曾沙养鞠须丰巢关蒯相查后江红游竺权逯盖益";  
    private static String girl="秀娟英华慧巧美娜静淑惠珠翠雅芝玉萍红娥玲芬芳燕彩春菊兰凤洁梅琳素云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧璐娅琦晶妍茜秋珊莎锦黛青倩婷姣婉娴瑾颖露瑶怡婵雁蓓纨仪荷丹蓉眉君琴蕊薇菁梦岚苑婕馨瑗琰韵融园艺咏卿聪澜纯毓悦昭冰爽琬茗羽希宁欣飘育滢馥筠柔竹霭凝晓欢霄枫芸菲寒伊亚宜可姬舒影荔枝思丽";  
    private static String boy="伟刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘";  
    private static final String[] email_suffix="@gmail.com,@yahoo.com,@msn.com,@hotmail.com,@aol.com,@ask.com,@live.com,@qq.com,@0355.net,@163.com,@163.net,@263.net,@3721.net,@yeah.net,@googlemail.com,@126.com,@sina.com,@sohu.com,@yahoo.com.cn".split(",");  
    private static final String[] ICON_PATH = {"http://bbcare.oss-cn-hangzhou.aliyuncs.com/image/1.jpg",
    		"http://bbcare.oss-cn-hangzhou.aliyuncs.com/image/17.jpg",
    		"http://bbcare.oss-cn-hangzhou.aliyuncs.com/image/4.jpg",
    		"http://bbcare.oss-cn-hangzhou.aliyuncs.com/image/14.jpg",
    		"http://bbcare.oss-cn-hangzhou.aliyuncs.com/image/15.jpg",
    		"http://bbcare.oss-cn-hangzhou.aliyuncs.com/image/18.jpg",
    		"http://bbcare.oss-cn-hangzhou.aliyuncs.com/image/6.jpg",
    		"http://bbcare.oss-cn-hangzhou.aliyuncs.com/image/16.jpg",
    		"http://bbcare.oss-cn-hangzhou.aliyuncs.com/image/8.jpg"};
    
    public static int getNum(int start,int end) {  
        return (int)(Math.random()*(end-start+1)+start);  
    }  
    
    
    /** 
     * 随机返回头像地址
     * @return String
     */  
    public static String getIconPath() {  
        return ICON_PATH[getNum(0, 8)];   
    }  
      
    /**
     * 返回六位随机密码
     * @return String
     */
    public static String getPasswoed(){
    	char[] cs = base.toCharArray();
    	
    	StringBuffer sb = new StringBuffer();
    	for (int i = 0; i < 6; i++) {
			sb.append(cs[getNum(0, base.length() - 1)]);
		}
    	
    	return sb.toString();
    }
    
    /** 
     * 返回Email 
     * @param lMin 最小长度 
     * @param lMax 最大长度 
     * @return 
     */  
    public static String getEmail(int lMin,int lMax) {  
        int length=getNum(lMin,lMax);  
        StringBuffer sb = new StringBuffer();       
        for (int i = 0; i < length; i++) {       
            int number = (int)(Math.random()*base.length());  
            sb.append(base.charAt(number));
        }  
        sb.append(email_suffix[(int)(Math.random()*email_suffix.length)]);  
        return sb.toString();     
    }  
  
    /** 
     * 随机生成手机号码 
     */
    private static String[] telFirst = "134,135,136,137,138,139,150,151,152,157,158,159,130,131,132,155,156,133,153".split(",");  
    private static String getTel() {  
        int index=getNum(0,telFirst.length-1);  
        String first=telFirst[index];  
        String second=String.valueOf(getNum(1,888)+10000).substring(1);  
        String thrid=String.valueOf(getNum(1,9100)+10000).substring(1);  
        return first+second+thrid;  
    }  
      
    /** 
     * 返回中文姓名 
     */  
    private static String name_sex = "";  
    private static String getChineseName() {  
        int index=getNum(0, firstName.length()-1);  
        String first=firstName.substring(index, index+1);  
        int sex=getNum(0,1);  
        String str=boy;  
        int length=boy.length();  
        if(sex==0){  
            str=girl;  
            length=girl.length();  
            name_sex = "F";  
        }else {  
            name_sex="N";  
        }
        index=getNum(0,length-1);  
        String second=str.substring(index, index+1);  
        int hasThird=getNum(0,1);  
        String third="";  
        if(hasThird==1){  
            index=getNum(0,length-1);  
            third=str.substring(index, index+1);  
        }  
        return first + second + third;  
    }  

    
    /* 根据开始时间和结束时间返回时间段内的时间集合 
     *  
     * @param begin
     * @param end 
     * @return List<String>
     */  
    public static List<String> getDatesBetweenTwoDate(String begin, String end) {  
        List<String> lDate = new LinkedList<String>();  
        lDate.add(begin);// 把开始时间加入集合  
        
        //把字符串转换成日期
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date endDate;
        Date beginDate;
		try {
			beginDate = dateFormat.parse(begin);
			endDate = dateFormat.parse(end);
		} catch (ParseException e) {
			throw new RuntimeException("日期格式输入错误!");
		}
        
        Calendar cal = Calendar.getInstance();  
        // 使用给定的 Date 设置此 Calendar 的时间  
        cal.setTime(beginDate);  
        boolean bContinue = true;  
        while (bContinue) {  
            // 根据日历的规则，为给定的日历字段添加或减去指定的时间量  
            cal.add(Calendar.DAY_OF_MONTH, 1);  
            // 测试此日期是否在指定日期之后  
            if (endDate.after(cal.getTime())) {  
                lDate.add(dateFormat.format(cal.getTime()));  
            } else {  
                break;  
            }  
        }  
        lDate.add(end);// 把结束时间加入集合  
        return lDate;  
    }  

    {
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    }
    
    
    /*
     * 根据注册日期随机生成宝宝生日
     * @param createTime
     * @return String
     */
    public static String getBirthday(String createTime){
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    	Date maxDate = null;
		try {
			maxDate = dateFormat.parse(createTime);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	long max = maxDate.getTime();
    	long min = max - 63158400000L;
        Calendar calendar = Calendar.getInstance();  

        //得到大于等于min小于max的double值  
        double randomDate = Math.random()*(max-min)+min;  
        //将double值舍入为整数，转化成long类型  
        calendar.setTimeInMillis(Math.round(randomDate));  
        Date date = calendar.getTime();
        return dateFormat.format(date);
    }
    
    //获取数据库连接对象
    public static Connection getConnection(){
    	Connection connection = null;
    	/*String url = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com:3306/bbcare_cloud?createDatabaseIfNotExist=true&amp;amp;useUnicode=true&amp;amp;characterEncoding=utf8mb4&amp;amp;zeroDateTimeBehavior=convertToNull&amp;amp;transformedBitIsBoolean=true";
    	String username = "test_zhangbw";
    	String password = "bw123456";
    	try {
			connection = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}*/
    	return connection;
    }
    
    /*
     * 判断数据库中有没有该手机号	true:存在 		false:不存在
     * @param conn
     * @param phoneNumber
     * @return boolean
     */
      
    public static boolean checkPhoneNumber(Connection conn,String phoneNumber){
    	boolean b = true;
    	try {
			PreparedStatement prepareStatement = conn.prepareStatement("select _accountId from t_account where _accountName = ?");
			prepareStatement.setString(1, phoneNumber);
			ResultSet resultSet = prepareStatement.executeQuery();
			b = resultSet.next();
			resultSet.close();
			prepareStatement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	return b;
    }
    
    
    public static void main(String[] args) {  
    	//获取数据库连接
    	Connection connection = getConnection();
    	
    	List<String> dates = getDatesBetweenTwoDate("2016-10-01", "2016-10-31");
    	
    	while(dates.size() > 0) {
    		
    		String createTime = dates.remove(0);
    		
    		for (int i = 0; i < getNum(180, 200); i++) {  
    			String accountId ="";
    			String babyId = "";
    			String accountName = getTel();
    			
    			//判断数据库是否存在该手机号,若存在就重新获取手机号
    			while (checkPhoneNumber(connection, accountName)) {
    				accountName = getTel();
				}
    			String iconPath = getIconPath();
    			String createTime2 = getCreateTime(createTime); //获取有时分秒的时间 
    			String nickName = getChineseName();
    			String password = getPasswoed();
    			String birthday = getBirthday(createTime);
    			
    			//向t_account插入数据
    			{
	    			String sql = "INSERT INTO t_account (_accountId,_accountName,_accountType,_iconPath,_password,_createTime,_accountSource,_firstLogin) VALUES(?,?,'U',?,?,?,'bbcareapp','N')";
	    			
	    			try {
		    				PreparedStatement statement = connection.prepareStatement(sql);
		    				statement.setString(1, accountId);
		    				statement.setString(2, accountName);
		    				statement.setString(3, iconPath);
		    				statement.setString(4, password);
		    				statement.setString(5, createTime2);
		    				
		    				statement.executeUpdate();
		    				statement.close();
		    			} catch (SQLException e) {
		    				
		    				e.printStackTrace();
		    			}
	    		}
    			
    			
    			//向t_baby插入数据
    			{
    				String sql = "INSERT INTO t_baby (_babyId,_babyName,_birthday,_gender,_iconPath,_realName,_writetime,_rongUserId,_unreadmsg,_userId,_userName,_firstLogin,_pregnancyWeek)" +
    						"VALUES(?,?,?,?,?,?,?,?,0,?,?,'N',?)";
    				
    				try {
	    				PreparedStatement statement = connection.prepareStatement(sql);
	    				statement.setString(1, babyId);
	    				statement.setString(2, nickName);
	    				statement.setString(3, birthday);
	    				statement.setString(4, name_sex);
	    				statement.setString(5, iconPath);
	    				statement.setString(6, nickName);
	    				statement.setString(7, createTime2);
	    				statement.setString(8, babyId);
	    				statement.setString(9, babyId);
	    				statement.setString(10, nickName);
	    				statement.setInt(11, getNum(250, 300));
	    				
	    				statement.executeUpdate();
	    				statement.close();
	    			} catch (SQLException e) {
	    				
	    				e.printStackTrace();
	    			}
    				
    			}
    			
    			//向t_baby_t_account插入数据
    			{
    				String sql = "INSERT INTO t_baby_t_account (_id,account_id,baby_id,_relationType,_status)" +
    						"VALUES(?,?,?,'05','01')";
    				
    				try {
	    				PreparedStatement statement = connection.prepareStatement(sql);
	    				statement.setString(1, "");
	    				statement.setString(2, accountId);
	    				statement.setString(3, babyId);
	    				
	    				statement.executeUpdate();
	    				statement.close();
	    			} catch (SQLException e) {
	    				
	    				e.printStackTrace();
	    			}
    				
    			}
    			
    			//向t_record插入记录
    			{
    				String sql = "INSERT INTO t_record VALUES(?)";
    				
    				try {
	    				PreparedStatement statement = connection.prepareStatement(sql);
	    				statement.setString(1, accountId);
	    				
	    				statement.executeUpdate();
	    				statement.close();
	    			} catch (SQLException e) {
	    				
	    				e.printStackTrace();
	    			}
    			}
    			System.out.println(accountId);
    			
    		}  
    	}

        try {
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }  
    
   /**
    * 根据指定日期随机返回当天6点到12点的一个时间点
    * @param time
    * @return	String
    */

    
    public static String getCreateTime(String time) {
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		Date date = null;
		try {
			long min = simpleDateFormat.parse(time).getTime() + 21600000L;
			long max = min + 86399999L - 21600000L;
	        Calendar calendar = Calendar.getInstance();  

	        //得到大于等于min小于max的double值  
	        double randomDate = Math.random()*(max-min)+min;  
	        //将double值舍入为整数，转化成long类型  
	        calendar.setTimeInMillis(Math.round(randomDate));  
	        date = calendar.getTime();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dateFormat.format(date);
	}
    
    
}
